// 引入
const express = require('express');
const db = require('../../utils/db');

// 配置路由
const router = express.Router();

// 编写接口
// 职业
router.all('/organization/show', (req, res) => {
    let atPage = req.query.page;
    console.log(req.query);
    let atCount = req.query.count;
    const sql2 = `SELECT * FROM 
    t_branch RIGHT JOIN t_post
    ON t_branch.b_id=t_post.p_b_id`
    const sql = `SELECT * FROM t_branch JOIN t_post
    ON t_branch.b_id=t_post.p_b_id LIMIT ${(atPage-1)*atCount},${atCount};`;
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            db.query(sql2, (err2, data2) => {
                if (err2) {
                    console.log(err2);
                    res.send({ error: 1, msg: err2 });
                } else {
                    res.send({ error: 0, msg: '渲染成功', data: data, total: data2.length });
                }
            })
        }
    })
});
// 渲染组织管理和创建组织的下拉1
router.all('/organization/showsel_branch', (req, res) => {
    const sql = `SELECT b_name FROM t_branch;`
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '渲染成功', data: data });
        }
    })
});
// 渲染组织管理和创建组织的下拉2
router.all('/organization/showsel_post', (req, res) => {
    let sql = `SELECT p_name FROM t_post;`
    if (req.query.val.length) {
        sql = `SELECT * FROM 
        t_branch JOIN t_post
        ON t_branch.b_id=t_post.p_b_id 
        WHERE b_name='${req.query.val}'
         ;`
    };
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '渲染成功', data: data });
        }
    })
});
// 渲染组织管理和创建组织的下拉3
router.all('/organization/showsel_branch_ID', (req, res) => {
    let sql = `SELECT b_id FROM t_branch WHERE b_name='${req.query.val}';`
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '渲染成功', data: data });
        }
    })
});
// 渲染组织管理和创建组织的下拉4
router.all('/organization/showsel_branch_num', (req, res) => {
    let sql = `SELECT b_num FROM t_branch WHERE b_name='${req.query.val}';`
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '渲染成功', data: data });
        }
    })
});

// 查找
router.all('/organization/que', (req, res) => {
    let sql
    if (req.body.val1.length && !req.body.val2.length) {
        sql = `SELECT * FROM t_branch JOIN t_post ON t_branch.b_id=t_post.p_b_id WHERE b_name='${req.body.val1}';`
    } else if (!req.body.val1.length && req.body.val2.length) {
        sql = `SELECT * FROM t_branch JOIN t_post ON t_branch.b_id=t_post.p_b_id WHERE p_name='${req.body.val2}';`
    } else {
        sql = `SELECT * FROM t_branch JOIN t_post ON t_branch.b_id=t_post.p_b_id WHERE b_name='${req.body.val1}' AND p_name='${req.body.val2}';`
    }

    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '查找成功', data: data, total: data.length });
        }
    })
})

// 班级
router.all('/class/show', (req, res) => {
    let atPage = req.query.page;
    console.log(req.query);
    let atCount = req.query.count;
    const sql2 = `SELECT *
    FROM t_department,t_major,t_class
    WHERE t_department.d_id=t_major.m_d_id AND
    t_major.m_id=t_class.c_m_id;`
    const sql = `SELECT *
    FROM t_department,t_major,t_class
    WHERE t_department.d_id=t_major.m_d_id AND
    t_major.m_id=t_class.c_m_id LIMIT ${(atPage-1)*atCount},${atCount};`;
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            db.query(sql2, (err2, data2) => {
                if (err2) {
                    console.log(err2);
                    res.send({ error: 1, msg: err2 });
                } else {
                    res.send({ error: 0, msg: '渲染成功', data: data, total: data2.length });
                }
            })
        }
    })
});
// 渲染班级的下拉1
router.all('/class/showsel_department', (req, res) => {
    const sql = `SELECT d_name FROM t_department;`
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '渲染成功', data: data });
        }
    })
});
// 渲染班级的下拉2
router.all('/class/showsel_major', (req, res) => {
    let sql = `SELECT m_name FROM t_major;`
    if (req.query.val.length) {
        sql = `SELECT * FROM 
        t_department JOIN t_major
        ON t_department.d_id=t_major.m_d_id 
        WHERE d_name='${req.query.val}';`
    };
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '渲染成功', data: data });
        }
    })
});
// 渲染班级的下拉3
router.all('/class/showsel_class', (req, res) => {
    let sql = `SELECT c_name FROM t_class;`
    if (req.query.val.length) {
        sql = `SELECT * FROM 
        t_major JOIN t_class
        ON t_major.m_id=t_class.c_m_id 
        WHERE m_name='${req.query.val}';`
    };
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '渲染成功', data: data });
        }
    })
});
// 渲染班级的下拉4
router.all('/class/showsel_class_m_id', (req, res) => {
    let sql = `SELECT * FROM t_major WHERE m_name='${req.query.val}';`
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '渲染成功', data: data });
        }
    })
});

// 查找班级
router.all('/class/que', (req, res) => {
    let sql
    if (req.body.val1.length && !req.body.val2.length && !req.body.val3.length) {
        sql = `SELECT * FROM
            (SELECT * FROM t_department,t_major,t_class WHERE t_department.d_id=t_major.m_d_id AND t_major.m_id=t_class.c_m_id) AS t1 WHERE d_name='${req.body.val1}';`
    } else if (!req.body.val1.length && req.body.val2.length && !req.body.val3.length) {
        sql = `SELECT * FROM
            (SELECT * FROM t_department,t_major,t_class WHERE t_department.d_id=t_major.m_d_id AND t_major.m_id=t_class.c_m_id) AS t1 WHERE m_name='${req.body.val2}';`
    } else if (!req.body.val1.length && !req.body.val2.length && req.body.val3.length) {
        sql = `SELECT * FROM
            (SELECT * FROM t_department,t_major,t_class WHERE t_department.d_id=t_major.m_d_id AND t_major.m_id=t_class.c_m_id) AS t1 WHERE c_name='${req.body.val3}';`
    } else if (!req.body.val1.length && req.body.val2.length && req.body.val3.length) {
        sql = `SELECT * FROM
            (SELECT * FROM t_department,t_major,t_class WHERE t_department.d_id=t_major.m_d_id AND t_major.m_id=t_class.c_m_id) AS t1 WHERE m_name='${req.body.val2}' AND c_name='${req.body.val3}';`
    } else if (req.body.val1.length && !req.body.val2.length && req.body.val3.length) {
        sql = `SELECT * FROM
            (SELECT * FROM t_department,t_major,t_class WHERE t_department.d_id=t_major.m_d_id AND t_major.m_id=t_class.c_m_id) AS t1 WHERE d_name='${req.body.val1}' AND c_name='${req.body.val3}';`
    } else if (req.body.val1.length && req.body.val2.length && !req.body.val3.length) {
        sql = `SELECT * FROM
            (SELECT * FROM t_department,t_major,t_class WHERE t_department.d_id=t_major.m_d_id AND t_major.m_id=t_class.c_m_id) AS t1 WHERE d_name='${req.body.val1}' AND m_name='${req.body.val2}';`
    } else {
        sql = `SELECT * FROM
            (SELECT * FROM t_department,t_major,t_class WHERE t_department.d_id=t_major.m_d_id AND t_major.m_id=t_class.c_m_id) AS t1 WHERE d_name='${req.body.val1}' AND m_name='${req.body.val2}' AND c_name='${req.body.val3}';`
    }
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '查找成功', data: data, total: data.length });
        }
    })
})

// 渲染部门编制内容
router.all('/organization/preparation', (req, res) => {
    let sql = `SELECT * FROM t_post;`
    if (req.body.b_name.length) {
        sql = `SELECT * FROM 
        t_branch JOIN t_post
        ON t_branch.b_id=t_post.p_b_id
        WHERE b_name='${req.body.b_name}';`
    }
    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '渲染成功', data: data });
        }
    })
});

// 渲染专业编制左边内容
router.all('/class/preparation_left', (req, res) => {
    let sql = `SELECT * FROM t_major;`
    if (req.body.d_name.length) {
        sql = `SELECT * FROM 
        t_major JOIN t_department
        ON t_major.m_d_id=t_department.d_id 
        WHERE d_name='${req.body.d_name}';`;
    }

    db.query(sql, (err, data) => {
        if (err) {
            console.log(err);
            res.send({ error: 1, msg: err });
        } else {
            res.send({ error: 0, msg: '查找成功', data: data, total: data.length });
        }
    })

})

// 导出
module.exports = router;